{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "3o8Qof7Cy165"
   },
   "source": [
    "# LAB 01:  Applying Feature Engineering to BigQuery ML Models \n",
    "\n",
    "**Learning Objectives**\n",
    "\n",
    "* Setup up the environment\n",
    "* Create the project dataset\n",
    "* Create the feature engineering training table\n",
    "* Create and evaluate the  benchmark/baseline model\n",
    "* Extract numeric features\n",
    "* Perform a feature cross\n",
    "* Evaluate model performance\n",
    "\n",
    "\n",
    "## Introduction \n",
    "In this notebook, we utilize feature engineering to improve the prediction of the fare amount for a taxi ride in New York City.  We will use BigQuery ML to build a taxifare prediction model, using feature engineering to improve and create a final model.\n",
    "\n",
    "In this lab we set up the environment, create the project dataset, create a feature engineering table, create and evaluate a benchmark model, extract numeric features, perform a feature cross and evaluate model performance.\n",
    "\n",
    "Each learning objective will correspond to a __#TODO__ in this student lab notebook -- try to complete this notebook first and then review the [solution notebook](../solution/feateng-solution_bqml.ipynb). **NOTE TO SELF**:  UPDATE HYPERLINK."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "hJ7ByvoXzpVI"
   },
   "source": [
    "### Set up environment variables and load necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your current GCP Project Name is: cloud-training-demos\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "export PROJECT=$(gcloud config list project --format \"value(core.project)\")\n",
    "echo \"Your current GCP Project Name is: \"$PROJECT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "PROJECT = \"cloud-training-demos\" # REPLACE WITH YOUR PROJECT NAME\n",
    "REGION = \"us-west1-b\" # REPLACE WITH YOUR BUCKET REGION e.g. us-central1\n",
    "\n",
    "# Do not change these\n",
    "os.environ[\"PROJECT\"] = PROJECT\n",
    "os.environ[\"REGION\"] = REGION\n",
    "os.environ[\"BUCKET\"] = PROJECT # DEFAULT BUCKET WILL BE PROJECT ID\n",
    "\n",
    "if PROJECT == \"your-gcp-project-here\":\n",
    "  print(\"Don't forget to update your PROJECT name! Currently:\", PROJECT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "mC9K9Dpx1ztf"
   },
   "source": [
    "Check that the Google BigQuery library is installed and if not, install it. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 609
    },
    "colab_type": "code",
    "id": "RZUQtASG10xO",
    "outputId": "5612d6b0-9730-476a-a28f-8fdc14f4ecde"
   },
   "outputs": [],
   "source": [
    "!pip freeze | grep google-cloud-bigquery==1.6.1 || pip install google-cloud-bigquery==1.6.1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "L0-vOB4y2BJM"
   },
   "source": [
    "## The source dataset\n",
    "\n",
    "Our dataset is hosted in [BigQuery](https://cloud.google.com/bigquery/). The taxi fare data is a publically available dataset, meaning anyone with a GCP account has access. Click [here](https://console.cloud.google.com/bigquery?project=bigquery-public-data&p=nyc-tlc&d=yellow&t=trips&page=table) to acess the dataset.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a BigQuery Dataset and Google Cloud Storage Bucket \n",
    "\n",
    "A BigQuery dataset is a container for tables, views, and models built with BigQuery ML. Let's create one called __feat_eng__ if we have not already done so in an earlier lab. We'll do the same for a GCS bucket for our project too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BigQuery dataset already exists, let's not recreate it.\n",
      "Bucket exists, let's not recreate it.\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "\n",
    "## Create a BigQuery dataset for feat_eng_TEST if it doesn't exist\n",
    "datasetexists=$(bq ls -d | grep -w feat_eng)\n",
    "\n",
    "if [ -n \"$datasetexists\" ]; then\n",
    "    echo -e \"BigQuery dataset already exists, let's not recreate it.\"\n",
    "\n",
    "else\n",
    "    echo \"Creating BigQuery dataset titled: feat_eng\"\n",
    "    \n",
    "    bq --location=US mk --dataset \\\n",
    "        --description 'Taxi Fare' \\\n",
    "        $PROJECT:feat_eng\n",
    "   echo \"\\nHere are your current datasets:\"\n",
    "   bq ls\n",
    "fi    \n",
    "    \n",
    "## Create GCS bucket if it doesn't exist already...\n",
    "exists=$(gcloud storage ls | grep -w gs://${PROJECT}/)\n",
    "\n",
    "if [ -n \"$exists\" ]; then\n",
    "   echo -e \"Bucket exists, let's not recreate it.\"\n",
    "    \n",
    "else\n",
    "   echo \"Creating a new GCS bucket.\"\n",
    "   gcloud storage buckets create -l ${REGION} gs://${PROJECT}\n",
    "   echo \"\\nHere are your current buckets:\"\n",
    "   gcloud storage ls\n",
    "fi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "b2TuS1s9vREL"
   },
   "source": [
    "## Create the training data table\n",
    "\n",
    "Since there is already a publicly available dataset, we can simply create the training data table.  Note the WHERE clause in the below query:  This clause allows us to TRAIN a portion of the data (e.g. one million rows versus a billion rows), which keeps your query costs down. \n",
    "\n",
    "* Note:  The dataset in the create table code below is the one created previously, e.g. \"feat_eng\".  The table name is \"feateng_training_data\"."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **Exercise**:   **RUN** the query to create the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "CMNRractvREL"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE feat_eng.feateng_training_data \n",
    "AS\n",
    "SELECT\n",
    "  (tolls_amount + fare_amount) AS fare_amount,\n",
    "  passenger_count*1.0 AS passengers,\n",
    "  pickup_datetime,\n",
    "  pickup_longitude AS pickuplon,\n",
    "  pickup_latitude AS pickuplat,\n",
    "  dropoff_longitude AS dropofflon,\n",
    "  dropoff_latitude AS dropofflat\n",
    "\n",
    "\n",
    "FROM `nyc-tlc.yellow.trips`\n",
    "WHERE MOD(ABS(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING))), 10000) = 1\n",
    " \n",
    "  AND fare_amount >= 2.5\n",
    "  AND passenger_count > 0\n",
    "  AND pickup_longitude > -78\n",
    "  AND pickup_longitude < -70\n",
    "  AND dropoff_longitude > -78\n",
    "  AND dropoff_longitude < -70\n",
    "  AND pickup_latitude > 37\n",
    "  AND pickup_latitude < 45\n",
    "  AND dropoff_latitude > 37\n",
    "  AND dropoff_latitude < 45\n",
    "  \n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "clnaaqQsXkwC"
   },
   "source": [
    "## Verify table creation\n",
    "\n",
    "Verify that you created the dataset.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>passengers</th>\n",
       "      <th>pickup_datetime</th>\n",
       "      <th>pickuplon</th>\n",
       "      <th>pickuplat</th>\n",
       "      <th>dropofflon</th>\n",
       "      <th>dropofflat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [fare_amount, passengers, pickup_datetime, pickuplon, pickuplat, dropofflon, dropofflat]\n",
       "Index: []"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "-- LIMIT 0 is a free query; this allows us to check that the table exists.\n",
    "SELECT * FROM feat_eng.feateng_training_data \n",
    "LIMIT 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "RhgXan8wvREN"
   },
   "source": [
    "### Benchmark Model:  Create the benchmark/baseline Model\n",
    "\n",
    "Next, you create a linear regression baseline model with no feature engineering.  Recall that a model in BigQuery ML represents what an ML system has learned from the training data.  A baseline model is a solution to a problem without applying any machine learning techniques.  \n",
    "\n",
    "When creating a BQML model, you must specify the model type (in our case linear regression) and the input label (fare_amount).  Note also that we are using the training data table as the data source."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "kb_5NlfU7oyT"
   },
   "source": [
    "#### Exercise:  Create the SQL statement to create the model \"Benchmark Model\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "2odQpUn_7yCe"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "# TODO: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "# SOLUTION\n",
    "CREATE OR REPLACE MODEL feat_eng.benchmark_model\n",
    "\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "\n",
    "SELECT \n",
    " fare_amount,\n",
    "  passengers,\n",
    "  pickup_datetime,\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat FROM feat_eng.feateng_training_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Tq2KYJOM9ULC"
   },
   "source": [
    "\n",
    "REMINDER:  The query takes several minutes to complete. After the first iteration is complete, your model (benchmark_model) appears in the navigation panel of the BigQuery web UI. Because the query uses a CREATE MODEL statement to create a model, you do not see query results.\n",
    "\n",
    "You can observe the model as it's being trained by viewing the Model stats tab in the BigQuery web UI. As soon as the first iteration completes, the tab is updated. The stats continue to update as each iteration completes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "HO5d50Eic-X1"
   },
   "source": [
    "Once the training is done, visit the [BigQuery Cloud Console](https://console.cloud.google.com/bigquery) and look at the model that has been trained. Then, come back to this notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "RSgIJqN6vREV"
   },
   "source": [
    "### Evaluate the benchmark model\n",
    "Note that BigQuery automatically split the data we gave it, and trained on only a part of the data and used the rest for evaluation.  After creating your model, you evaluate the performance of the regressor using the ML.EVALUATE function. The ML.EVALUATE function evaluates the predicted values against the actual data.\n",
    "\n",
    "NOTE: The results are also displayed in the [BigQuery Cloud Console](https://console.cloud.google.com/bigquery) under the **Evaluation** tab."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise: Review the learning and eval statistics for the benchmark_model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>training_run</th>\n",
       "      <th>iteration</th>\n",
       "      <th>loss</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>duration_ms</th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>74.434806</td>\n",
       "      <td>68.880152</td>\n",
       "      <td>None</td>\n",
       "      <td>13164</td>\n",
       "      <td>8.627561</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   training_run  iteration       loss  eval_loss learning_rate  duration_ms  \\\n",
       "0             0          0  74.434806  68.880152          None        13164   \n",
       "\n",
       "       rmse  \n",
       "0  8.627561  "
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#Eval statistics on the held out data.\n",
    "SELECT *, SQRT(loss) AS rmse FROM ML.TRAINING_INFO(MODEL feat_eng.benchmark_model)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5.213535</td>\n",
       "      <td>68.880152</td>\n",
       "      <td>0.258107</td>\n",
       "      <td>3.790249</td>\n",
       "      <td>0.226068</td>\n",
       "      <td>0.226133</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             5.213535           68.880152                0.258107   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               3.790249  0.226068            0.226133  "
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.benchmark_model)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "xJGbfYuD8a9d"
   },
   "source": [
    "**NOTE:** Because you performed a linear regression, the results include the following columns:\n",
    "\n",
    "*   mean_absolute_error\n",
    "*   mean_squared_error\n",
    "*   mean_squared_log_error\n",
    "*   median_absolute_error\n",
    "*   r2_score\n",
    "*   explained_variance\n",
    "\n",
    "**Resource** for an explanation of the regression metrics:  [Regression Metrics](https://https://joshlawman.com/metrics-regression/)\n",
    "\n",
    "**Mean squared error** (MSE) - Measures the difference between the values our model predicted using the test set and the actual values. You can also think of it as the distance between your regression (best fit) line and the predicted values. \n",
    "\n",
    "**Root mean squared error** (RMSE) - The primary evaluation metric for this ML problem is the root mean-squared error. RMSE measures the difference between the predictions of a model, and the observed values. A large RMSE is equivalent to a large average error, so smaller values of RMSE are better. One nice property of RMSE is that the error is given in the units being measured, so you can tell very directly how incorrect the model might be on unseen data.\n",
    "\n",
    "**R2**:  An important metric in the evaluation results is the R2 score. The R2 score is a statistical measure that determines if the linear regression predictions approximate the actual data. 0 indicates that the model explains none of the variability of the response data around the mean. 1 indicates that the model explains all the variability of the response data around the mean."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "p_21sAIR7LZw"
   },
   "source": [
    "#### Exercise: Write a SQL query to take the SQRT() of the mean squared error as your loss metric for evaluation for the benchmark_model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "# TODO "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "cellView": "form",
    "colab": {},
    "colab_type": "code",
    "id": "8mAXRTvbvRES"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>8.299407</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rmse\n",
       "0  8.299407"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL feat_eng.benchmark_model)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "nW6fnqAW8vyI"
   },
   "source": [
    "#### Model 1:  EXTRACT DayOfWeek from the pickup_datetime feature.\n",
    "\n",
    "* As you recall, DayOfWeek is an enum representing the 7 days of the week. This factory allows the enum to be obtained from the int value. The int value follows the ISO-8601 standard, from 1 (Monday) to 7 (Sunday). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise:  EXTRACT DayOfWeek from the pickup_datetime feature.\n",
    "* Create a model titled \"model_1\" from the benchmark model and extract out the DayofWeek."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "hkXTdLM--vpj"
   },
   "outputs": [],
   "source": [
    "# TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "ZQ0kT2jN-vpm"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "CREATE OR REPLACE MODEL feat_eng.model_1\n",
    "\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "\n",
    "SELECT \n",
    " fare_amount,\n",
    "  passengers,\n",
    "  pickup_datetime,\n",
    "  EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat FROM feat_eng.feateng_training_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "T24XjIJgdLCH"
   },
   "source": [
    "Once the training is done, visit the [BigQuery Cloud Console](https://console.cloud.google.com/bigquery) and look at the model that has been trained. Then, come back to this notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "SRLxpccX_Tin"
   },
   "source": [
    "#### Exercise: Create two distinct SQL statements to see the TRAINING and EVALUATION metrics of model_1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Km_c9Ioq_Ti3"
   },
   "outputs": [],
   "source": [
    "#Create the SQL statements to extract Model_1 TRAINING metrics.  \n",
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create the SQL statements to extract Model_1 EVALUATION metrics.  \n",
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>training_run</th>\n",
       "      <th>iteration</th>\n",
       "      <th>loss</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>duration_ms</th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>72.440724</td>\n",
       "      <td>88.953232</td>\n",
       "      <td>None</td>\n",
       "      <td>17251</td>\n",
       "      <td>8.511212</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   training_run  iteration       loss  eval_loss learning_rate  duration_ms  \\\n",
       "0             0          0  72.440724  88.953232          None        17251   \n",
       "\n",
       "       rmse  \n",
       "0  8.511212  "
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT *, SQRT(loss) AS rmse FROM ML.TRAINING_INFO(MODEL feat_eng.model_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5.287076</td>\n",
       "      <td>88.953232</td>\n",
       "      <td>0.260932</td>\n",
       "      <td>3.713439</td>\n",
       "      <td>0.08481</td>\n",
       "      <td>0.084811</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             5.287076           88.953232                0.260932   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               3.713439   0.08481            0.084811  "
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.model_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Create the SQL statements to review model_1 training information.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "wf-9FBmL_Ti_"
   },
   "source": [
    "#### Exercise: Write a SQL query to take the SQRT() of the mean squared error as your loss metric for evaluation for model_1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "7n2kdbiH_TjA"
   },
   "outputs": [],
   "source": [
    "#Create the SQL statement to EVALUATE Model_1 here.  \n",
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "CsVBzNef_TjC"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>9.431502</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rmse\n",
       "0  9.431502"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL feat_eng.model_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Lw30UexH8v9P"
   },
   "source": [
    "### Model 2:  EXTRACT hourofday from the pickup_datetime feature\n",
    "\n",
    "As you recall, **pickup_datetime** is stored as a TIMESTAMP, where the Timestamp format is retrieved in the standard output format – year-month-day hour:minute:second (e.g. 2016-01-01 23:59:59).  Hourofaday returns the integer number representing the hour number of the given date."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise:  EXTRACT hourofday from the pickup_datetime feature.\n",
    "* Create a model titled \"model_2\"\n",
    "* EXTRACT the hourofday from the pickup_datetime feature to improve our model's rmse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Fa520N8KCIgp"
   },
   "outputs": [],
   "source": [
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "FHeqcYz-B9F1"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "CREATE OR REPLACE MODEL feat_eng.model_2\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  #pickup_datetime,\n",
    "  EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "  EXTRACT(HOUR FROM pickup_datetime) AS hourofday,\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat\n",
    "  \n",
    "FROM `feat_eng.feateng_training_data`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "u2TU5nG6CiZe"
   },
   "source": [
    "#### Exercise:  Create two SQL statements to evaluate the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "h2yjF6uGCiZh"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5.256421</td>\n",
       "      <td>70.709518</td>\n",
       "      <td>0.262399</td>\n",
       "      <td>3.895509</td>\n",
       "      <td>0.236668</td>\n",
       "      <td>0.236768</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             5.256421           70.709518                0.262399   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               3.895509  0.236668            0.236768  "
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.model_2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "bhfabG8XCiZm"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>8.408895</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rmse\n",
       "0  8.408895"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL feat_eng.model_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "vbOSxv6BDqB-"
   },
   "source": [
    "### Model 3:  Feature cross dayofweek and hourofday \n",
    "\n",
    "First, let’s allow the model to learn traffic patterns by creating a new feature that combines the time of day and day of week (this is called a feature cross). \n",
    "\n",
    "* Modify model_2 to create a feature cross that combines the time of day and day of week.  Note:  CAST DAYOFWEEK and HOUR as strings.  Name the model \"model_3\".  \n",
    "\n",
    "* In this lab, we will modify the SQL to first use the CONCAT function to concatenate (feature cross) the dayofweek and hourofday features.  Then, we will use the ML.FEATURE_CROSS, BigQuery's new pre-processing feature cross function.\n",
    "\n",
    "Note:  BQML by default assumes that numbers are numeric features, and strings are categorical features.  We need to convert these features to strings because the Neural Network will treat 1,2,3,4,5,6,7 as numeric values.  Thus, there is no way to distinguish the time of day and day of week \"numerically.\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise: Create the SQL statement to feature cross the dayofweek and hourofday using the CONCAT function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "R_i3w3H7FXUW"
   },
   "outputs": [],
   "source": [
    "# TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "F7l02C9KFMy7"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "CREATE OR REPLACE MODEL feat_eng.revised_model_3\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  #pickup_datetime,\n",
    "  #EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "  #EXTRACT(HOUR FROM pickup_datetime) AS hourofday,\n",
    "  CONCAT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING), \n",
    "        CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING)) AS hourofday,\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat\n",
    "  \n",
    "FROM `feat_eng.feateng_training_data`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise:  Create two SQL statements to evaluate the model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5.500029</td>\n",
       "      <td>93.260577</td>\n",
       "      <td>0.27445</td>\n",
       "      <td>3.811464</td>\n",
       "      <td>0.080263</td>\n",
       "      <td>0.080492</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             5.500029           93.260577                 0.27445   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               3.811464  0.080263            0.080492  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.model_3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>9.657152</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rmse\n",
       "0  9.657152"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL feat_eng.model_3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "FbSRbuJ-fYtK"
   },
   "source": [
    "### Model 4:  Apply the ML.FEATURE_CROSS clause to categorical features\n",
    "\n",
    "BigQuery ML now has ML.FEATURE_CROSS, a pre-processing function that performs a feature cross.  \n",
    "\n",
    "* ML.FEATURE_CROSS generates a STRUCT feature with all combinations of crossed categorical features, except for 1-degree items (the original features) and self-crossing items.  \n",
    "\n",
    "* Syntax:  ML.FEATURE_CROSS(STRUCT(features), degree)\n",
    "\n",
    "* The feature parameter is a categorical features separated by comma to be crossed. The maximum number of input features is 10. Unnamed feature is not allowed in features. Duplicates are not allowed in features.\n",
    "\n",
    "* Degree(optional): The highest degree of all combinations. Degree should be in the range of [1, 4]. Default to 2.\n",
    "\n",
    "Output: The function outputs a STRUCT of all combinations except for 1-degree items (the original features) and self-crossing items, with field names as concatenation of original feature names and values as the concatenation of the column string values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise:  The ML.Feature_Cross statement contains errors. Correct the errors and run the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "Z3U2FxVklrlU"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE MODEL feat_eng.model_4\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  #pickup_datetime,\n",
    "  #EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "  #EXTRACT(HOUR FROM pickup_datetime) AS hourofday,\n",
    "  #CONCAT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING), \n",
    "        #CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING)) AS hourofday,\n",
    "  ML.FEATURE_CROSS(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING),\n",
    "  CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday)) AS day_hr,\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat\n",
    "  \n",
    "FROM `feat_eng.feateng_training_data`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "CREATE OR REPLACE MODEL feat_eng.model_4\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  #pickup_datetime,\n",
    "  #EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "  #EXTRACT(HOUR FROM pickup_datetime) AS hourofday,\n",
    "  #CONCAT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING), \n",
    "        #CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING)) AS hourofday,\n",
    " ML.FEATURE_CROSS(STRUCT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek,\n",
    "  CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday)) AS day_hr,\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat\n",
    "  \n",
    "FROM `feat_eng.feateng_training_data`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "G6tpoYhcIgs4"
   },
   "source": [
    "#### Exercise:  Create two SQL statements to evaluate the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "t10fGqSfIgtA"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5.500029</td>\n",
       "      <td>93.260577</td>\n",
       "      <td>0.27445</td>\n",
       "      <td>3.807548</td>\n",
       "      <td>0.080263</td>\n",
       "      <td>0.080492</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             5.500029           93.260577                 0.27445   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               3.807548  0.080263            0.080492  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.model_4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "uC-gyvAmIgtE"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>9.657152</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rmse\n",
       "0  9.657152"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL feat_eng.model_4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "kSsEA8hrEg8t"
   },
   "source": [
    "# LAB 02:  Applying Feature Engineering to BigQuery ML Models\n",
    "\n",
    "**Learning Objectives**\n",
    "\n",
    "* Derive coordinate features\n",
    "* Feature cross coordinate features\n",
    "* Evalute model performance\n",
    "* Code cleanup\n",
    "\n",
    "\n",
    "\n",
    "## Introduction \n",
    "In this notebook, we derive coordinate features, feature cross coordinate features, evaluate model performance, and cleanup the code."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "2NAPkAlEEg6C"
   },
   "source": [
    "### Model 5:  Feature cross coordinate features to create a Euclidean feature\n",
    "\n",
    "\n",
    "Pickup coordinate:\n",
    "*  pickup_longitude AS pickuplon\n",
    "*  pickup_latitude AS pickuplat\n",
    " \n",
    "\n",
    "\n",
    "Dropoff coordinate:\n",
    "*   #dropoff_longitude AS dropofflon\n",
    "*   #dropoff_latitude AS dropofflat\n",
    "\n",
    "**NOTES**:\n",
    "* The pick-up and drop-off longitude and latitude data are crucial to predicting the fare amount as fare amounts in NYC taxis are largely determined by the distance traveled.  Assuch, we need to  teach the model the Euclidean distance between the pick-up and drop-off points.  \n",
    "\n",
    "* Recall that latitude and longitude allows us to specify any location on Earth using a set of coordinates.  In our training data set, we restricted our data points to only pickups and drop offs within NYC. NYC has an approximate longitude range of -74.05 to -73.75 and a latitude range of 40.63 to 40.85.\n",
    "\n",
    "* The dataset contains information regarding the pickup and drop off coordinates. However, there is no information regarding the distance between the pickup and drop off points. Therefore, we create a new feature that calculates the distance between each pair of pickup and drop off points. We can do this using the Euclidean Distance, which is the straight-line distance between any two coordiante points.\n",
    "\n",
    "* We need to convert those coordinates into a single column of a spatial data type.  We will use the The ST_Distance function, which returns the minimum distance between two spatial objects.  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "kCYniJnejNz0"
   },
   "source": [
    "#### Exercise:  Derive a coordinate feature.\n",
    "\n",
    "* Convert the feature coordinates into a single column of a spatial data type. Use the The ST_Distance function, which returns the minimum distance between two spatial objects.\n",
    "SAMPLE CODE:\n",
    "ST_Distance(ST_GeogPoint(value1,value2), ST_GeogPoint(value3, value4)) AS euclidean\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "B6e4JoqrjvdI"
   },
   "outputs": [],
   "source": [
    "# TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "P8mFocaKj9oA"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#Solution\n",
    "CREATE OR REPLACE MODEL feat_eng.model_5\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  #pickup_datetime,\n",
    "  #EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "  #EXTRACT(HOUR FROM pickup_datetime) AS hourofday,\n",
    "  #CONCAT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING), \n",
    "        #CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING)) AS hourofday,\n",
    "  ML.FEATURE_CROSS(STRUCT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek,\n",
    "  CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday)) AS day_hr,\n",
    "  #pickuplon,\n",
    "  #pickuplat,\n",
    "  #dropofflon,\n",
    "  #dropofflat,\n",
    "  ST_Distance(ST_GeogPoint(pickuplon, pickuplat), ST_GeogPoint(dropofflon, dropofflat)) \n",
    "  AS euclidean\n",
    "  \n",
    "FROM `feat_eng.feateng_training_data`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "uy7eK6iXlYPu"
   },
   "source": [
    "#### Exercise:  Create two SQL statements to evaluate the model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "2n-hmfy7lYP2"
   },
   "outputs": [],
   "source": [
    "  # TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "9atctYyGlYP7"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>3.121311</td>\n",
       "      <td>31.229717</td>\n",
       "      <td>0.106923</td>\n",
       "      <td>2.218179</td>\n",
       "      <td>0.661492</td>\n",
       "      <td>0.661507</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             3.121311           31.229717                0.106923   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               2.218179  0.661492            0.661507  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.model_5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "Lk42mvjzlYQE"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5.588356</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rmse\n",
       "0  5.588356"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL feat_eng.model_5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "uUoQmADkmlnV"
   },
   "source": [
    "### Model 6:  Feature cross  pick-up and drop-off locations features\n",
    "\n",
    "In this section, we feature cross the pick-up and drop-off locations so that the model can learn pick-up-drop-off pairs that will require tolls.\n",
    "\n",
    "This step takes the geographic point corresponding to the pickup point and grids to a 0.1-degree-latitude/longitude grid (approximately 8km x 11km in New York—we should experiment with finer resolution grids as well). Then, it concatenates the pickup and dropoff grid points to learn “corrections” beyond the Euclidean distance associated with pairs of pickup and dropoff locations.\n",
    "\n",
    "Because the lat and lon by themselves don't have meaning, but only in conjunction, it may be useful to treat the fields as a pair instead of just using them as numeric values. However, lat and lon are continuous numbers, so we have to discretize them first. That's what SnapToGrid does. \n",
    "\n",
    "**REMINDER**: The ST_GEOGPOINT creates a GEOGRAPHY with a single point. ST_GEOGPOINT creates a point from the specified FLOAT64 longitude and latitude parameters and returns that point in a GEOGRAPHY value.  The ST_Distance function returns the minimum distance between two spatial objectsa.  It also returns meters for geographies and SRID units for geometrics.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Excercise:  The following SQL statement is incorrect.  Modify the code to feature cross the pick-up and drop-off locations features. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "iVBfDDECoSBD"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "#TODO \n",
    "CREATE OR REPLACE MODEL feat_eng.model_6\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  #pickup_datetime,\n",
    "  #EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "  #EXTRACT(HOUR FROM pickup_datetime) AS hourofday,\n",
    "  #CONCAT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING), \n",
    "        #CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING)) AS hourofday,\n",
    "  ML.FEATURE_CROSS(STRUCT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek,\n",
    "  CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday)) AS day_hr,\n",
    "  #pickuplon,\n",
    "  #pickuplat,\n",
    "  #dropofflon,\n",
    "  #dropofflat,\n",
    " ST_AsText(ST_SnapToGrid(ST_GeogPoint(pickuplat,pickuplon,pickuplat), 0.05)), \n",
    " ST_AsText(ST_GeogPoint(dropofflon, dropofflat,dropofflon), 0.04)) AS pickup_and_dropoff\n",
    "  \n",
    "FROM `feat_eng.feateng_training_data`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "7VjZawfZpQ7Y"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "CREATE OR REPLACE MODEL feat_eng.model_6\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  #pickup_datetime,\n",
    "  #EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "  #EXTRACT(HOUR FROM pickup_datetime) AS hourofday,\n",
    "  ML.FEATURE_CROSS(STRUCT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek,\n",
    "  CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday)) AS day_hr,\n",
    "  #pickuplon,\n",
    "  #pickuplat,\n",
    "  #dropofflon,\n",
    "  #dropofflat,\n",
    "  ST_Distance(ST_GeogPoint(pickuplon, pickuplat), ST_GeogPoint(dropofflon, dropofflat)) \n",
    "  AS euclidean,\n",
    "   CONCAT(ST_AsText(ST_SnapToGrid(ST_GeogPoint(pickuplon, pickuplat), 0.01)),\n",
    "     ST_AsText(ST_SnapToGrid(ST_GeogPoint(dropofflon, dropofflat), 0.01)))\n",
    "  AS pickup_and_dropoff\n",
    "  \n",
    "FROM `feat_eng.feateng_training_data`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "zvOfj_k1qijv"
   },
   "source": [
    "#### Exercise:  Create two SQL statements to evaluate the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "G8rM09jLqij4"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2.678752</td>\n",
       "      <td>34.885692</td>\n",
       "      <td>0.088598</td>\n",
       "      <td>1.485065</td>\n",
       "      <td>0.640979</td>\n",
       "      <td>0.642637</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             2.678752           34.885692                0.088598   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               1.485065  0.640979            0.642637  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.model_6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "CQUfOjPlqij8"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5.906411</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rmse\n",
       "0  5.906411"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL feat_eng.model_6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "I1dW3JLHrP5Z"
   },
   "source": [
    "### Code Clean Up"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise:  Clean up the code to see where we are\n",
    "\n",
    "Remove all the commented statements in the SQL statement.  We should now have a total of five input features for our model.  \n",
    "1. fare_amount\n",
    "2. passengers\n",
    "3. day_hr\n",
    "4. euclidean\n",
    "5. pickup_and_dropoff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "-UxZXY18rWG8"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#Solution\n",
    "CREATE OR REPLACE MODEL feat_eng.model_6\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  ML.FEATURE_CROSS(STRUCT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek,\n",
    "  CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday)) AS day_hr,\n",
    "  ST_Distance(ST_GeogPoint(pickuplon, pickuplat), ST_GeogPoint(dropofflon, dropofflat)) \n",
    "  AS euclidean,\n",
    "   CONCAT(ST_AsText(ST_SnapToGrid(ST_GeogPoint(pickuplon, pickuplat), 0.01)),\n",
    "     ST_AsText(ST_SnapToGrid(ST_GeogPoint(dropofflon, dropofflat), 0.01)))\n",
    "  AS pickup_and_dropoff\n",
    "  \n",
    "FROM `feat_eng.feateng_training_data`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LAB 03:  Applying Feature Engineering to BigQuery ML Models\n",
    "\n",
    "**Learning Objectives**\n",
    "\n",
    "* Apply the BUCKETIZE function\n",
    "* Apply the TRANSFORM clause\n",
    "* Apply L2 Regularization\n",
    "* Model evaluation\n",
    "\n",
    "\n",
    "## Introduction \n",
    "In this notebook, we apply the BUCKETIZE function, the TRANSFORM clause, L2 Regularization, and perform model evaluation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## BQML's Pre-processing functions:\n",
    "\n",
    "Here are some of the preprocessing functions in BigQuery ML:\n",
    "* ML.FEATURE_CROSS(STRUCT(features))    does a feature cross of all the combinations\n",
    "* ML.POLYNOMIAL_EXPAND(STRUCT(features), degree)    creates x, x^2, x^3, etc.\n",
    "* ML.BUCKETIZE(f, split_points)   where split_points is an array "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Model 7:  Apply the BUCKETIZE Function \n",
    "\n",
    "\n",
    "##### BUCKETIZE \n",
    "Bucketize is a pre-processing function that creates \"buckets\" (e.g bins) - e.g. it bucketizes a continuous numerical feature into a string feature with bucket names as the value.\n",
    "\n",
    "* ML.BUCKETIZE(feature, split_points)\n",
    "\n",
    "* feature: A numerical column.\n",
    "\n",
    "* split_points: Array of numerical points to split the continuous values in feature into buckets. With n split points (s1, s2 … sn), there will be n+1 buckets generated. \n",
    "\n",
    "* Output: The function outputs a STRING for each row, which is the bucket name. bucket_name is in the format of bin_<bucket_number>, where bucket_number starts from 1.\n",
    "\n",
    "* Currently, our model uses the ST_GeogPoint function to derive the pickup and dropoff feature.  In this lab, we use the BUCKETIZE function to create the pickup and dropoff feature."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise: Apply the BUCKETIZE function.\n",
    "* Hint:  Create a model_7."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "CREATE OR REPLACE MODEL feat_eng.model_7\n",
    "OPTIONS\n",
    "  (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount'])  \n",
    "AS\n",
    "AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  SQRT( (pickuplon-dropofflon)*(pickuplon-dropofflon) + (pickuplat-dropofflat)*(pickuplat-dropofflat) ) AS euclidean, \n",
    "  ML.FEATURE_CROSS(STRUCT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek,\n",
    "  CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday)) AS day_hr,\n",
    "  CONCAT(\n",
    "     ML.BUCKETIZE(pickuplon, GENERATE_ARRAY(-78, -70, 0.01)),\n",
    "     ML.BUCKETIZE(pickuplat, GENERATE_ARRAY(37, 45, 0.01)),\n",
    "     ML.BUCKETIZE(dropofflon, GENERATE_ARRAY(-78, -70, 0.01)),\n",
    "     ML.BUCKETIZE(dropofflat, GENERATE_ARRAY(37, 45, 0.01))\n",
    "  ) AS pickup_and_dropoff\n",
    "  \n",
    "FROM `feat_eng.feateng_training_data`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "AVPXGKZ374v7"
   },
   "source": [
    "#### Exercise:  Create three SQL statements to EVALUATE the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>training_run</th>\n",
       "      <th>iteration</th>\n",
       "      <th>loss</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>duration_ms</th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>11.231835</td>\n",
       "      <td>33.172360</td>\n",
       "      <td>0.4</td>\n",
       "      <td>6434</td>\n",
       "      <td>3.351393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>13.263899</td>\n",
       "      <td>35.919936</td>\n",
       "      <td>0.8</td>\n",
       "      <td>5898</td>\n",
       "      <td>3.641964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>20.108475</td>\n",
       "      <td>36.559006</td>\n",
       "      <td>0.4</td>\n",
       "      <td>6533</td>\n",
       "      <td>4.484247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>70.490524</td>\n",
       "      <td>81.719679</td>\n",
       "      <td>0.2</td>\n",
       "      <td>3746</td>\n",
       "      <td>8.395864</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   training_run  iteration       loss  eval_loss  learning_rate  duration_ms  \\\n",
       "0             0          3  11.231835  33.172360            0.4         6434   \n",
       "1             0          2  13.263899  35.919936            0.8         5898   \n",
       "2             0          1  20.108475  36.559006            0.4         6533   \n",
       "3             0          0  70.490524  81.719679            0.2         3746   \n",
       "\n",
       "       rmse  \n",
       "0  3.351393  \n",
       "1  3.641964  \n",
       "2  4.484247  \n",
       "3  8.395864  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT *, SQRT(loss) AS rmse FROM ML.TRAINING_INFO(MODEL feat_eng.model_7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2.674187</td>\n",
       "      <td>33.17236</td>\n",
       "      <td>0.089913</td>\n",
       "      <td>1.51821</td>\n",
       "      <td>0.635728</td>\n",
       "      <td>0.637299</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             2.674187            33.17236                0.089913   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0                1.51821  0.635728            0.637299  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.model_7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>5.759545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rmse\n",
       "0  5.759545"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL feat_eng.model_7)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Final Model:  Apply the TRANSFORM clause and L2 Regularization\n",
    "\n",
    "Before we perform our prediction, we should encapsulate the entire feature set in a TRANSFORM clause.  BigQuery ML now supports defining data transformations during model creation, which will be automatically applied during prediction and evaluation. This is done through the TRANSFORM clause in the existing CREATE MODEL statement. By using the TRANSFORM clause, user specified transforms during training will be automatically applied during model serving (prediction, evaluation, etc.) \n",
    "\n",
    "In our case, we are using the TRANSFORM clause to separate out the raw input data from the TRANSFORMED features.  The input columns of the TRANSFORM clause is the query_expr (AS SELECT part).  The output columns of TRANSFORM from select_list are used in training. These transformed columns are post-processed with standardization for numerics and one-hot encoding for categorical variables by default. \n",
    "\n",
    "The advantage of encapsulating features in the TRANSFORM is the client code doing the PREDICT doesn't change. Our model improvement is transparent to client code. Note that the TRANSFORM clause MUST be placed after the CREATE statement.\n",
    "\n",
    "##### L2 Regularization\n",
    "Sometimes, the training RMSE is quite reasonable, but the evaluation RMSE illustrate more error. Given the severity of the delta between the EVALUATION RMSE and the TRAINING RMSE, it may be an indication of overfitting. When we do feature crosses, we run into the risk of overfitting (for example, when a particular day-hour combo doesn't have enough taxirides)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise:  Apply the TRANSFORM clause and L2 Regularization to the final model and run the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "CREATE OR REPLACE MODEL feat_eng.final_model\n",
    "TRANSFORM(\n",
    "  fare_amount, \n",
    "    SQRT( (pickuplon-dropofflon)*(pickuplon-dropofflon) + (pickuplat-dropofflat)*(pickuplat-dropofflat) ) AS euclidean, \n",
    "    ML.FEATURE_CROSS(STRUCT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek,\n",
    "        CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday)) AS day_hr,\n",
    "   CONCAT(\n",
    "     ML.BUCKETIZE(pickuplon, GENERATE_ARRAY(-78, -70, 0.01)),\n",
    "     ML.BUCKETIZE(pickuplat, GENERATE_ARRAY(37, 45, 0.01)),\n",
    "     ML.BUCKETIZE(dropofflon, GENERATE_ARRAY(-78, -70, 0.01)),\n",
    "     ML.BUCKETIZE(dropofflat, GENERATE_ARRAY(37, 45, 0.01))\n",
    "  ) AS pickup_and_dropoff\n",
    ")\n",
    "OPTIONS(input_label_cols=['fare_amount'], model_type='linear_reg', l2_reg=0.1) \n",
    "AS\n",
    "\n",
    "SELECT * FROM feat_eng.feateng_training_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise:  Create three SQL statements to EVALUATE the final model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>training_run</th>\n",
       "      <th>iteration</th>\n",
       "      <th>loss</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>duration_ms</th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11.120628</td>\n",
       "      <td>21.654845</td>\n",
       "      <td>None</td>\n",
       "      <td>232685</td>\n",
       "      <td>3.334761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   training_run  iteration       loss  eval_loss learning_rate  duration_ms  \\\n",
       "0             0          0  11.120628  21.654845          None       232685   \n",
       "\n",
       "       rmse  \n",
       "0  3.334761  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT *, SQRT(loss) AS rmse FROM ML.TRAINING_INFO(MODEL feat_eng.final_model)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2.262816</td>\n",
       "      <td>21.654845</td>\n",
       "      <td>0.068715</td>\n",
       "      <td>1.358259</td>\n",
       "      <td>0.756688</td>\n",
       "      <td>0.756689</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             2.262816           21.654845                0.068715   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               1.358259  0.756688            0.756689  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.final_model)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>4.653477</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       rmse\n",
       "0  4.653477"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT SQRT(mean_squared_error) AS rmse FROM ML.EVALUATE(MODEL feat_eng.final_model)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Sa5UfRE2Az1w"
   },
   "source": [
    "# LAB 04:  Applying Feature Engineering to BigQuery ML ModelS\n",
    "\n",
    "**Learning Objectives**\n",
    "\n",
    "* Create a prediction model\n",
    "* Evalute model performance\n",
    "* Examine the role of feature engineering on the ML problem\n",
    "\n",
    "\n",
    "\n",
    "## Introduction \n",
    "In this notebook, we create prediction models, evaluate model performance, and examine the role of feature engineering on the ML problem."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "DIuFS56o_F2a"
   },
   "source": [
    "### Prediction Model\n",
    "\n",
    "\n",
    "Now that you have evaluated your model, the next step is to use it to predict an outcome. You use your model to predict the taxifare amount. \n",
    "The ML.PREDICT function is used to predict results using your model: feat_eng.final_model.  \n",
    "\n",
    "Since this is a regression model (predicting a continuous numerical value), the best way to see how it performed is to evaluate the difference between the value predicted by the model and the benchmark score. We can do this with an ML.PREDICT query.\n",
    "\n",
    "#### Exercise:  Modify **THIS INCORRECT SQL STRATEMENT** before running the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "qys4liHN_cI4"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "#TODO\n",
    "SELECT * FROM ML.EVALUATE(MODEL feat_eng.benchmark_model, (\n",
    "    -73.982683 AS pickuplon,\n",
    "    40.742104 AS pickuplat,\n",
    "    -73.983766 AS dropofflon,\n",
    "    40.755174 AS dropofflat,\n",
    "    3.0 AS passengers,\n",
    "    TIMESTAMP('2019-06-03 04:21:29.769443 UTC) AS pickup_datetime\n",
    "))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "29cuS1CbADE3"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>predicted_fare_amount</th>\n",
       "      <th>pickuplon</th>\n",
       "      <th>pickuplat</th>\n",
       "      <th>dropofflon</th>\n",
       "      <th>dropofflat</th>\n",
       "      <th>passengers</th>\n",
       "      <th>pickup_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6.081999</td>\n",
       "      <td>-73.982683</td>\n",
       "      <td>40.742104</td>\n",
       "      <td>-73.983766</td>\n",
       "      <td>40.755174</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2019-06-03 04:21:29.769443+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   predicted_fare_amount  pickuplon  pickuplat  dropofflon  dropofflat  \\\n",
       "0               6.081999 -73.982683  40.742104  -73.983766   40.755174   \n",
       "\n",
       "   passengers                  pickup_datetime  \n",
       "0         3.0 2019-06-03 04:21:29.769443+00:00  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION\n",
    "# This is the prediction query FOR heading 1.3 miles uptown in New York City on 2019-06-03 at 04:21:29.769443 UTC time with 3 passengers.\n",
    "SELECT * FROM ML.PREDICT(MODEL feat_eng.final_model, (\n",
    "  SELECT \n",
    "    -73.982683 AS pickuplon,\n",
    "    40.742104 AS pickuplat,\n",
    "    -73.983766 AS dropofflon,\n",
    "    40.755174 AS dropofflat,\n",
    "    3.0 AS passengers,\n",
    "    TIMESTAMP('2019-06-03 04:21:29.769443 UTC') AS pickup_datetime\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise:  Remove passengers from the prediction model  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#TODO "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>predicted_fare_amount</th>\n",
       "      <th>pickuplon</th>\n",
       "      <th>pickuplat</th>\n",
       "      <th>dropofflon</th>\n",
       "      <th>dropofflat</th>\n",
       "      <th>pickup_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6.081999</td>\n",
       "      <td>-73.982683</td>\n",
       "      <td>40.742104</td>\n",
       "      <td>-73.983766</td>\n",
       "      <td>40.755174</td>\n",
       "      <td>2019-06-03 04:21:29.769443+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   predicted_fare_amount  pickuplon  pickuplat  dropofflon  dropofflat  \\\n",
       "0               6.081999 -73.982683  40.742104  -73.983766   40.755174   \n",
       "\n",
       "                   pickup_datetime  \n",
       "0 2019-06-03 04:21:29.769443+00:00  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#SOLUTION - remove passengers\n",
    "\n",
    "SELECT * FROM ML.PREDICT(MODEL feat_eng.final_model, (\n",
    "  SELECT \n",
    "    -73.982683 AS pickuplon,\n",
    "    40.742104 AS pickuplat,\n",
    "    -73.983766 AS dropofflon,\n",
    "    40.755174 AS dropofflat,\n",
    "    TIMESTAMP('2019-06-03 04:21:29.769443 UTC') AS pickup_datetime\n",
    "))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What can you conclude when the feature passengers is removed from the prediction model?\n",
    "ANSWER:  Number of passengers at this pickup_datetime and location does not affect fare.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lab Summary: \n",
    "Our ML problem was to develop a model to predict taxi fare based on distance -- from one point to another in New York City. \n",
    "Using feature engineering, we were able to predict a taxi fare of $6.08 in New York City, with an R2 score of .75, and an RMSE of 4.653 based upon the distance travelled."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise: Create a RMSE summary table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Markdown table generator:  http://www.tablesgenerator.com/markdown_tables\n",
    "Create a RMSE summary table:\n",
    "benchmark_model = 8.299407\n",
    "model_1 = 9.431 --EXTRACT DayOfWeek from the pickup_datetime feature\n",
    "model_2 = 8.408 --EXTRACT hourofday from the pickup_datetime feature\n",
    "model_3 = 9.657 --Feature cross dayofweek and hourofday -Feature Cross does lead ot overfitting\n",
    "model_4 = 9.657 --Apply the ML.FEATURE_CROSS clause to categorical features\n",
    "model_5 = 5.588 --Feature cross coordinate features to create a Euclidean feature\n",
    "model_6 = 5.906 --Feature cross  pick-up and drop-off locations features\n",
    "model_7 = 5.750 --Apply the BUCKETIZE function\n",
    "final_model = 4.653 --Apply the TRANSFORM clause and L2 Regularization "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Model       | RMSE  | Description                                                                     |\n",
    "|-------------|-------|---------------------------------------------------------------------------------|\n",
    "| model_1     | 9.431 | --EXTRACT DayOfWeek from the pickup_datetime feature                            |\n",
    "| model_2     | 8.408 | --EXTRACT hourofday from the pickup_datetime feature                            |\n",
    "| model_3     | 9.657 | --Feature cross dayofweek and hourofday -Feature Cross does lead to overfitting |\n",
    "| model_4     | 9.657 | --Apply the ML.FEATURE_CROSS clause to categorical features                     |\n",
    "| model_5     | 5.588 | --Feature cross coordinate features to create a Euclidean feature               |\n",
    "| model_6     | 5.906 | --Feature cross  pick-up and drop-off locations features                        |\n",
    "| model_7     | 5.75  | --Apply the BUCKETIZE function                                                  |\n",
    "| final_model | 4.653 | --Apply the TRANSFORM clause and L2 Regularization                              |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Excercise:  Visualization - Plot a bar chart."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAjYAAAGzCAYAAAA8I13DAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAAPYQAAD2EBqD+naQAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAIABJREFUeJzt3XmczeX///HnMThjHSMz1jGWbBHJlugzZMvySYWSbRAixIgyFUPSJFlbbJ80diI+SUUSKXsqWfoSLeYjW2HGjIxm5vr90c35dZqhOWNmzpnL4367vW/1vt7X+31e15nt6Xovx2GMMQIAALBAHm8XAAAAkFUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AHxShQoV1Lt370zt63A4NG7cuCytB0DuQLABslBMTIwcDodryZs3r8qWLavevXvrxIkTafo3a9ZMDodDVapUSfd4GzdudB1r1apVbtv279+vzp07KzQ0VP7+/ipbtqxatWql1157za1fhQoV3Gr663Lfffdddzxbtmxx9V28eHG6fZo0aSKHw6FatWpd91i+6OzZsxo2bJiqV6+uAgUKKDg4WA0bNtQzzzyjhIQEb5cHIBPyersAwEYvvPCCKlasqMuXL2vnzp2KiYnRF198oQMHDsjf39+tr7+/v44ePardu3erYcOGbtuWLFkif39/Xb582a19+/btat68ucqXL6/+/furVKlSio2N1c6dOzVjxgwNHTrUrf8dd9yhp556Kk2dZcqUydB4/P39tXTpUvXo0cOt/aefftL27dvTjCk3OHfunOrXr6/4+Hj17dtX1atX12+//aZvv/1Ws2bN0qBBg1S4cGFvlwnAQwQbIBu0bdtW9evXlyT169dPJUqU0KRJk7R27Vo9/PDDbn0rV66s5ORkLVu2zC3YXL58WWvWrFH79u317rvvuu0zceJEBQQEaM+ePSpWrJjbtjNnzqSpp2zZsmlCiSfatWuntWvX6tdff1WJEiVc7UuXLlXJkiVVpUoVnT9/PtPH94a33npLx48f17Zt23T33Xe7bYuPj1f+/Pm9VJn3JCYmqlChQt4uA7ghnIoCcsA999wjSTp27Fi62x999FGtWLFCqamprrb3339fly5dShOErh6nZs2aaUKNJAUHB2dR1f9fx44d5XQ6tXLlSrf2pUuX6uGHH5afn1+afZKTkzVhwgRVrlxZTqdTFSpU0LPPPqukpCS3fsYYvfjiiypXrpwKFiyo5s2b6+DBg+nWceHCBQ0fPlwhISFyOp269dZbNWnSJLf3LaOOHTsmPz8/3XXXXWm2FS1a1G0W6lrX+zRr1kzNmjVzrV89dffOO+9o/PjxKlu2rIoUKaLOnTsrLi5OSUlJGj58uIKDg1W4cGH16dMnzfvhcDg0ZMgQrVy5UrfddpsKFCigxo0ba//+/ZKkOXPm6NZbb5W/v7+aNWumn376yW3/zz//XF26dFH58uXldDoVEhKiiIgI/f777279evfurcKFC+vYsWNq166dihQpou7duysqKkr58uXT2bNn04x3wIABKlasWJoZRMCXEGyAHHD1j09gYGC627t166aTJ09qy5YtrralS5eqRYsW6QaV0NBQ7d27VwcOHMjQ6//xxx/69ddf0yx//2N3LQULFlTHjh21bNkyV9u+fft08OBBdevWLd19+vXrp7Fjx+rOO+/UtGnTFBYWpujoaHXt2tWt39ixYzVmzBjVqVNHkydPVqVKldS6dWslJia69bt06ZLCwsK0ePFi9erVSzNnzlSTJk0UGRmpESNGZGgcfxUaGqqUlBQtWrTI433/SXR0tDZs2KDRo0erb9++Wr16tQYOHKi+ffvqyJEjGjdunB566CHFxMRo0qRJafb//PPP9dRTTyk8PFzjxo3Td999pw4dOuiNN97QzJkz9cQTT2jUqFHasWOH+vbt67bvypUrdenSJQ0aNEivvfaa2rRpo9dee029evVK8zrJyclq06aNgoOD9eqrr6pTp07q2bOnkpOTtWLFCre+V65c0apVq9SpU6dceeoRNxEDIMu8/fbbRpL55JNPzNmzZ01sbKxZtWqVCQoKMk6n08TGxrr1DwsLMzVr1jTGGFO/fn3z2GOPGWOMOX/+vMmfP79ZsGCB2bx5s5FkVq5c6drv448/Nn5+fsbPz880btzYPP3002bDhg3mypUraWoKDQ01ktJdoqOjrzuev772unXrjMPhMMePHzfGGDNq1ChTqVKlNOMwxphvvvnGSDL9+vVzO97IkSONJPPpp58aY4w5c+aMyZ8/v2nfvr1JTU119Xv22WeNJBMeHu5qmzBhgilUqJA5cuSI2zFHjx5t/Pz8XHUZY4wkExUVdd2xnTp1ygQFBRlJpnr16mbgwIFm6dKl5sKFC2n6hoaGutVyVVhYmAkLC0vzftWqVcvta/Hoo48ah8Nh2rZt67Z/48aNTWhoqFubJON0Os2PP/7oapszZ46RZEqVKmXi4+Nd7ZGRkUaSW99Lly6lqTM6Oto4HA7z888/u9rCw8ONJDN69Og0/Rs3bmwaNWrk1rZ69WojyWzevDlNf8CXMGMDZIOWLVsqKChIISEh6ty5swoVKqS1a9eqXLly19ynW7duWr16tetfxn5+fnrwwQfT7duqVSvt2LFD999/v/bt26dXXnlFbdq0UdmyZbV27do0/Rs1aqSNGzemWR599NEMj6l169YqXry4li9fLmOMli9ffs39P/zwQ0lKM5Ny9QLmDz74QJL0ySef6MqVKxo6dKgcDoer3/Dhw9Mcc+XKlbrnnnsUGBjoNuvUsmVLpaSkaOvWrRkeiySVLFlS+/bt08CBA3X+/HnNnj1b3bp1U3BwsCZMmCBjjEfH+6tevXopX758rvVGjRrJGJNmdqVRo0aKjY1VcnKyW3uLFi1UoUIFt36S1KlTJxUpUiRN+w8//OBqK1CggOv/ExMT9euvv+ruu++WMUZff/11mloHDRqUbv27du1yO3W6ZMkShYSEKCws7LpjB7yNYANkgzfeeEMbN27UqlWr1K5dO/36669yOp3X3adr166Ki4vTRx99pCVLlqhDhw5uf8T+rkGDBlq9erXOnz+v3bt3KzIyUhcvXlTnzp116NAht74lSpRQy5Yt0yyhoaEZHlO+fPnUpUsXLV26VFu3blVsbOw1T0P9/PPPypMnj2699Va39lKlSqlYsWL6+eefXf0kpbndPSgoKM1pu++//17r169XUFCQ29KyZUtJ6V80/U9Kly6tWbNm6eTJkzp8+LBmzpypoKAgjR07Vm+99ZbHx7uqfPnybusBAQGSpJCQkDTtqampiouLy/T+ktwu3D5+/Lh69+6t4sWLq3DhwgoKCnKFkb+/Tt68edMN24888oicTqeWLFni2m/dunXq3r27WwAFfBF3RQHZoGHDhq67oh544AE1bdpU3bp10+HDh695C3Hp0qXVrFkzTZkyRdu2bUtzJ9S15M+fXw0aNFCDBg1UtWpV9enTRytXrlRUVFSWjeeqbt26afbs2Ro3bpzq1Kmj22677br9s/KPYGpqqlq1aqWnn3463e1Vq1bN9LEdDoeqVq2qqlWrqn379qpSpYqWLFmifv36ubanJyUlJd0Lp9Nru17732eHMrt/SkqKWrVqpXPnzumZZ55R9erVVahQIZ04cUK9e/dOc5G10+lUnjxp/30bGBioDh06aMmSJRo7dqxWrVqlpKSkG7qzDsgpBBsgm/n5+Sk6OlrNmzfX66+/rtGjR1+zb7du3dSvXz8VK1ZM7dq18/i1roapkydPZrre62natKnKly+vLVu2pHvR61WhoaFKTU3V999/rxo1arjaT58+rQsXLrhmiq7+9/vvv1elSpVc/c6ePZvm9vHKlSsrISHBNUOTXSpVqqTAwEC39zAwMFAXLlxI0/fnn392q9vb9u/fryNHjmjBggVuFwtv3LjR42P16tVLHTt21J49e7RkyRLVrVtXNWvWzMpygWzBqSggBzRr1kwNGzbU9OnTr3urbOfOnRUVFaU333zzus9R2bx5c7rXgFy9tqVatWo3XnQ6HA6HZs6cqaioKPXs2fOa/a6GsunTp7u1T506VZLUvn17SX9ei5QvXz699tprbuP5+36S9PDDD2vHjh3asGFDmm0XLlxIc53KP9m1a1eaO68kaffu3frtt9/c3sPKlStr586dunLliqtt3bp1io2N9eg1s9vVGZ2/vpfGGM2YMcPjY7Vt29b1/KXPPvuM2RrkGszYADlk1KhR6tKli2JiYjRw4MB0+wQEBGToM46GDh2qS5cu6cEHH1T16tV15coVbd++XStWrFCFChXUp08ft/4nTpxI9yMRChcurAceeMCjcXTs2FEdO3a8bp86deooPDxcc+fO1YULFxQWFqbdu3drwYIFeuCBB9S8eXNJf15LM3LkSEVHR6tDhw5q166dvv76a3300UduDwKU/nz/1q5dqw4dOqh3796qV6+eEhMTtX//fq1atUo//fRTmn2uZ9GiRVqyZIkefPBB1atXT/nz59d3332n+fPny9/fX88++6yrb79+/bRq1Srdd999evjhh3Xs2DEtXrxYlStX9uCdy37Vq1dX5cqVNXLkSJ04cUJFixbVu+++m6mHJ+bLl09du3bV66+/Lj8/P48uNAe8iWAD5JCHHnpIlStX1quvvqr+/ftf83qJjHj11Ve1cuVKffjhh5o7d66uXLmi8uXL64knntDzzz+f5sF933zzTbozLKGhoR4Hm4z6z3/+o0qVKikmJkZr1qxRqVKlFBkZmebanxdffFH+/v6aPXu2Nm/erEaNGunjjz92zepcVbBgQX322Wd66aWXtHLlSi1cuFBFixZV1apVNX78eNeFtBn1+OOPq2DBgtq0aZPee+89xcfHKygoSK1bt1ZkZKTq1q3r6tumTRtNmTJFU6dO1fDhw1W/fn2tW7cu3Y+p8KZ8+fLp/fff15NPPqno6Gj5+/vrwQcf1JAhQ1SnTh2Pj9erVy+9/vrratGihUqXLp0NFQNZz2Fu5J5GAIC19u3bpzvuuEMLFy687qlHwJdwjQ0AIF3z5s1T4cKF9dBDD3m7FCDDOBUFAHDz/vvv69ChQ5o7d66GDBnCB2MiV+FUFADATYUKFXT69Gm1adNGixYtuu6DIgFfQ7ABAADW4BobAABgDYINAACwhvUXD6empuqXX35RkSJF+PA2AAByCWOMLl68qDJlyqT7mWbXYn2w+eWXX9J8Ii4AAMgdYmNj0/0U+mvxarDZunWrJk+erL179+rkyZNas2aN21NQjTGKiorSvHnzdOHCBTVp0kSzZs1SlSpVMvwaV6/mj42NVdGiRbN8DAAAIOvFx8crJCTE47vyvBpsEhMTVadOHfXt2zfdB0C98sormjlzphYsWKCKFStqzJgxatOmjQ4dOiR/f/8MvcbV009FixYl2AAAkMt4ehmJV4NN27Zt1bZt23S3GWM0ffp0Pf/8864P3Fu4cKFKliyp//73v+ratWtOlgoAAHIBn70r6scff9SpU6fUsmVLV1tAQIAaNWqkHTt2XHO/pKQkxcfHuy0AAODm4LPB5tSpU5KkkiVLurWXLFnStS090dHRCggIcC1cOAwAwM3DZ4NNZkVGRiouLs61xMbGerskAACQQ3w22JQqVUqSdPr0abf206dPu7alx+l0ui4U5oJhAABuLj4bbCpWrKhSpUpp06ZNrrb4+Hjt2rVLjRs39mJlAADAV3n1rqiEhAQdPXrUtf7jjz/qm2++UfHixVW+fHkNHz5cL774oqpUqeK63btMmTJuz7oBAAC4yqvB5ssvv1Tz5s1d6yNGjJAkhYeHKyYmRk8//bQSExM1YMAAXbhwQU2bNtX69esz/AwbAABwc3EYY4y3i8hO8fHxCggIUFxcHNfbAACQS2T277fPXmMDAADgKYINAACwBsEGAABYg2ADAACsQbABAADW8Ort3oAvmbbxiLdLyJSIVlU96n+zjBPAzYkZGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANXiOzQ3Irc8DkXgmCADATszYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACsQbABAADWINgAAABrEGwAAIA1CDYAAMAaBBsAAGANgg0AALAGwQYAAFiDYAMAAKxBsAEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACsQbABAADWINgAAABrEGwAAIA1CDYAAMAaBBsAAGANgg0AALBGXm8XAN83beMRb5eQaRGtqnq7BABADmLGBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACs4dPBJiUlRWPGjFHFihVVoEABVa5cWRMmTJAxxtulAQAAH+TTnxU1adIkzZo1SwsWLFDNmjX15Zdfqk+fPgoICNCTTz7p7fIAAICP8elgs337dnXs2FHt27eXJFWoUEHLli3T7t27vVwZAADwRT59Kuruu+/Wpk2bdOTIn58uvW/fPn3xxRdq27btNfdJSkpSfHy82wIAAG4OPj1jM3r0aMXHx6t69ery8/NTSkqKJk6cqO7du19zn+joaI0fPz4HqwQAAL7Cp2ds3nnnHS1ZskRLly7VV199pQULFujVV1/VggULrrlPZGSk4uLiXEtsbGwOVgwAALzJp2dsRo0apdGjR6tr166SpNtvv10///yzoqOjFR4enu4+TqdTTqczJ8sEAAA+wqdnbC5duqQ8edxL9PPzU2pqqpcqAgAAvsynZ2z+/e9/a+LEiSpfvrxq1qypr7/+WlOnTlXfvn29XRoAAPBBPh1sXnvtNY0ZM0ZPPPGEzpw5ozJlyujxxx/X2LFjvV0aAADwQT4dbIoUKaLp06dr+vTp3i4FAADkAj59jQ0AAIAnCDYAAMAaBBsAAGANgg0AALAGwQYAAFiDYAMAAKxBsAEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACsQbABAADWINgAAABrEGwAAIA1CDYAAMAaBBsAAGANgg0AALAGwQYAAFiDYAMAAKxBsAEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACsQbABAADWINgAAABrEGwAAIA1CDYAAMAaBBsAAGANgg0AALAGwQYAAFiDYAMAAKxBsAEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArOHzwebEiRPq0aOHbrnlFhUoUEC33367vvzyS2+XBQAAfFBebxdwPefPn1eTJk3UvHlzffTRRwoKCtL333+vwMBAb5cGAAB8kE8Hm0mTJikkJERvv/22q61ixYrX3ScpKUlJSUmu9fj4+GyrDwAA+BafPhW1du1a1a9fX126dFFwcLDq1q2refPmXXef6OhoBQQEuJaQkJAcqhYAAHibTwebH374QbNmzVKVKlW0YcMGDRo0SE8++aQWLFhwzX0iIyMVFxfnWmJjY3OwYgAA4E0+fSoqNTVV9evX10svvSRJqlu3rg4cOKDZs2crPDw83X2cTqecTmdOlgkAAHyET8/YlC5dWrfddptbW40aNXT8+HEvVQQAAHyZT8/YNGnSRIcPH3ZrO3LkiEJDQ71UEQD4jmkbj3i7hEyLaFXV2yXAUj49YxMREaGdO3fqpZde0tGjR7V06VLNnTtXgwcP9nZpAADAB/l0sGnQoIHWrFmjZcuWqVatWpowYYKmT5+u7t27e7s0AADgg3z6VJQkdejQQR06dPB2GQAAIBfw6RkbAAAAT/j8jA0A4ObGRdLwBDM2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACswV1RAKyUW++k4S4a4MYwYwMAAKxBsAEAANYg2AAAAGt4FGzOnDlz3e3JycnavXv3DRUEAACQWR4Fm9KlS7uFm9tvv12xsbGu9d9++02NGzfOuuoAAAA84FGwMca4rf/000/6448/rtsHAAAgp2T5NTYOhyOrDwkAAJAhXDwMAACs4dED+hwOhy5evCh/f38ZY+RwOJSQkKD4+HhJcv0XAADAGzwKNsYYVa1a1W29bt26buucigIAAN7iUbDZvHlzdtUBAABwwzwKNmFhYdlVBwAAwA3zKNgkJycrJSVFTqfT1Xb69GnNnj1biYmJuv/++9W0adMsLxIAACAjPAo2/fv3V/78+TVnzhxJ0sWLF9WgQQNdvnxZpUuX1rRp0/Tee++pXbt22VIsAADA9Xh0u/e2bdvUqVMn1/rChQuVkpKi77//Xvv27dOIESM0efLkLC8SAAAgIzwKNidOnFCVKlVc65s2bVKnTp0UEBAgSQoPD9fBgweztkIAAIAM8ijY+Pv76/fff3et79y5U40aNXLbnpCQkHXVAQAAeMCjYHPHHXdo0aJFkqTPP/9cp0+f1r333uvafuzYMZUpUyZrKwQAAMggjy4eHjt2rNq2bat33nlHJ0+eVO/evVW6dGnX9jVr1qhJkyZZXiQAAEBGePwcm7179+rjjz9WqVKl1KVLF7ftd9xxhxo2bJilBQIAAGSUR8FGkmrUqKEaNWqku23AgAE3XBAAAEBmeRRstm7dmqF+//rXvzJVDAAAwI3wKNg0a9bM9SGXxph0+zgcDqWkpNx4ZQAAAB7yKNgEBgaqSJEi6t27t3r27KkSJUpkV10AAAAe8+h275MnT2rSpEnasWOHbr/9dj322GPavn27ihYtqoCAANcCAADgDR4Fm/z58+uRRx7Rhg0b9H//93+qXbu2hgwZopCQED333HNKTk7OrjoBAAD+kUfB5q/Kly+vsWPH6pNPPlHVqlX18ssvKz4+PitrAwAA8Eimgk1SUpKWLl2qli1bqlatWipRooQ++OADFS9ePKvrAwAAyDCPLh7evXu33n77bS1fvlwVKlRQnz599M477xBoAACAT/Ao2Nx1110qX768nnzySdWrV0+S9MUXX6Tpd//992dNdQAAAB7w+MnDx48f14QJE665nefYAAAAb/Eo2KSmpv5jn0uXLmW6GAAAgBuR6bui/i4pKUlTp05VpUqVsuqQAAAAHvEo2CQlJSkyMlL169fX3Xffrf/+97+SpPnz56tixYqaNm2aIiIisqVQAACAf+LRqaixY8dqzpw5atmypbZv364uXbqoT58+2rlzp6ZOnaouXbrIz88vu2oFAMBa0zYe8XYJmRLRqqq3S3DjUbBZuXKlFi5cqPvvv18HDhxQ7dq1lZycrH379rk+HBMAAMBbPDoV9b///c91m3etWrXkdDoVERFBqAEAAD7Bo2CTkpKi/Pnzu9bz5s2rwoULZ3lRAAAAmeHRqShjjHr37i2n0ylJunz5sgYOHKhChQq59Vu9enXWVQgAAJBBHgWb8PBwt/UePXpkaTEAAAA3wqNg8/bbb2dXHQAAADcsyx7QBwAA4G0EGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACskauCzcsvvyyHw6Hhw4d7uxQAAOCDck2w2bNnj+bMmaPatWt7uxQAAOCjckWwSUhIUPfu3TVv3jwFBgZet29SUpLi4+PdFgAAcHPIFcFm8ODBat++vVq2bPmPfaOjoxUQEOBaQkJCcqBCAADgC3w+2CxfvlxfffWVoqOjM9Q/MjJScXFxriU2NjabKwQAAL4ir7cLuJ7Y2FgNGzZMGzdulL+/f4b2cTqdcjqd2VwZAADwRT4dbPbu3aszZ87ozjvvdLWlpKRo69atev3115WUlCQ/Pz8vVggAAHyJTwebFi1aaP/+/W5tffr0UfXq1fXMM88QagAAgBufDjZFihRRrVq13NoKFSqkW265JU07AACAz188DAAAkFE+PWOTni1btni7BAAA4KOYsQEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACsQbABAADWINgAAABrEGwAAIA1CDYAAMAaBBsAAGANgg0AALAGwQYAAFiDYAMAAKxBsAEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACsQbABAADWINgAAABrEGwAAIA1CDYAAMAaBBsAAGANgg0AALAGwQYAAFiDYAMAAKxBsAEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwhk8Hm+joaDVo0EBFihRRcHCwHnjgAR0+fNjbZQEAAB/l08Hms88+0+DBg7Vz505t3LhRf/zxh1q3bq3ExERvlwYAAHxQXm8XcD3r1693W4+JiVFwcLD27t2rf/3rX+nuk5SUpKSkJNd6fHx8ttYIAAB8h0/P2PxdXFycJKl48eLX7BMdHa2AgADXEhISklPlAQAAL8s1wSY1NVXDhw9XkyZNVKtWrWv2i4yMVFxcnGuJjY3NwSoBAIA3+fSpqL8aPHiwDhw4oC+++OK6/ZxOp5xOZw5VBQAAfEmuCDZDhgzRunXrtHXrVpUrV87b5QAAAB/l08HGGKOhQ4dqzZo12rJliypWrOjtkgAAgA/z6WAzePBgLV26VO+99560ymrNAAAPIElEQVSKFCmiU6dOSZICAgJUoEABL1cHAAB8jU9fPDxr1izFxcWpWbNmKl26tGtZsWKFt0sDAAA+yKdnbIwx3i4BAADkIj49YwMAAOAJgg0AALAGwQYAAFiDYAMAAKxBsAEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACsQbABAADWINgAAABrEGwAAIA1CDYAAMAaBBsAAGANgg0AALAGwQYAAFiDYAMAAKxBsAEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAaxBsAACANQg2AADAGgQbAABgDYINAACwBsEGAABYg2ADAACsQbABAADWINgAAABrEGwAAIA1CDYAAMAaBBsAAGANgg0AALAGwQYAAFiDYAMAAKxBsAEAANYg2AAAAGsQbAAAgDUINgAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1sgVweaNN95QhQoV5O/vr0aNGmn37t3eLgkAAPggnw82K1as0IgRIxQVFaWvvvpKderUUZs2bXTmzBlvlwYAAHyMzwebqVOnqn///urTp49uu+02zZ49WwULFtT8+fO9XRoAAPAxeb1dwPVcuXJFe/fuVWRkpKstT548atmypXbs2JHuPklJSUpKSnKtx8XFSZLi4+OzvL7LiQlZfsyc4sn7wTh9m6ff24zTt90M37MS40xPbh1ndvx9/etxjTGe7Wh82IkTJ4wks337drf2UaNGmYYNG6a7T1RUlJHEwsLCwsLCYsESGxvrUXbw6RmbzIiMjNSIESNc66mpqTp37pxuueUWORwOL1bmmfj4eIWEhCg2NlZFixb1djnZ4mYYo8Q4bcM47XEzjFHKveM0xujixYsqU6aMR/v5dLApUaKE/Pz8dPr0abf206dPq1SpUunu43Q65XQ63dqKFSuWbTVmt6JFi+aqb8TMuBnGKDFO2zBOe9wMY5Ry5zgDAgI83senLx7Onz+/6tWrp02bNrnaUlNTtWnTJjVu3NiLlQEAAF/k0zM2kjRixAiFh4erfv36atiwoaZPn67ExET16dPH26UBAAAf4zdu3Lhx3i7iemrVqqVixYpp4sSJevXVVyVJS5YsUbVq1bxcWfbz8/NTs2bNlDevz+fPTLsZxigxTtswTnvcDGOUbp5xSpLDGE/vowIAAPBNPn2NDQAAgCcINgAAwBoEGwAAYA2CDQAAsAbBBgCALGCM0YABA1S8eHE5HA4VK1ZMw4cPz9LXGDdunO64444sPWZW6N27tx544IEM99+yZYscDocuXLiQ5bUQbHKJJ598UvXq1ZPT6fTJb+qssG/fPj366KMKCQlRgQIFVKNGDc2YMcPbZWWp3377Tffdd5/KlCkjp9OpkJAQDRkyJNs+RM4X/PbbbypXrly2/RLzNofDkWZZvny5t8vKFjExMapdu7b8/f0VHByswYMHe7ukLBUTE5Pu19PhcOjMmTP/uP/69esVExOjdevW6eTJkzpy5IgmTJiQA5Xjr+y/od0iffv21a5du/Ttt996u5RssXfvXgUHB2vx4sUKCQnR9u3bNWDAAPn5+WnIkCHeLi9L5MmTRx07dtSLL76ooKAgHT16VIMHD9a5c+e0dOlSb5eXLR577DHVrl1bJ06c8HYp2ebtt9/Wfffd51rPzR/jci1Tp07VlClTNHnyZDVq1EiJiYn66aefvF1WlnrkkUfcvo7SnzMRly9fVnBw8D/uf+zYMZUuXVp33313dpWIjMjUx27jhoSFhZkhQ4aYYcOGmWLFipng4GAzd+5ck5CQYHr37m0KFy5sKleubD788MM0+0ZFRZk6dep4oWrP3cg4r3riiSdM8+bNc7Bqz2TFGGfMmGHKlSuXg1V7LrPjfPPNN01YWJjZtGmTkWTOnz/vpRFkTGbGKcmsWbPGi1V7ztNxnjt3zhQoUMB88sknXq7cMzf683nmzBmTL18+s3Dhwn98rfDwcLdPpA4NDTVhYWFm2LBhrj6hoaFm4sSJpk+fPqZw4cImJCTEzJkzx+04Tz/9tKlSpYopUKCAqVixonn++efNlStXXNs9+RsQHh5uOnbsaCZOnGiCg4NNQECAGT9+vPnjjz/MyJEjTWBgoClbtqyZP3++237ffvutad68ufH39zfFixc3/fv3NxcvXnRtT05ONhERESYgIMAUL17cjBo1yvTq1ct07NjR1SclJcW89NJLpkKFCsbf39/Url3brFy50rV98+bN2fY7gVNRXrJgwQKVKFFCu3fv1tChQzVo0CB16dJFd999t7766iu1bt1aPXv21KVLl7xd6g250XHGxcWpePHiOVy1Z25kjL/88otWr16tsLAwL1TuGU/HeejQIb3wwgtauHCh8uTJPb9qMvP1HDx4sEqUKKGGDRtq/vz5MrnguaeejHPjxo1KTU3ViRMnVKNGDZUrV04PP/ywYmNjvT2Mf3QjP58LFy5UwYIF1blz5398nRkzZuiFF15QuXLldPLkSe3ZsyfdflOmTFH9+vX19ddf64knntCgQYN0+PBh1/YiRYooJiZGhw4d0owZMzRv3jxNmzYt0+P/9NNP9csvv2jr1q2aOnWqoqKi1KFDBwUGBmrXrl0aOHCgHn/8cf3vf/+TJCUmJqpNmzYKDAzUnj17tHLlSn3yySdus+ZTpkxRTEyM5s+fry+++ELnzp3TmjVr3F43OjpaCxcu1OzZs3Xw4EFFRESoR48e+uyzzzI9lgzL8qiEfxQWFmaaNm3qWk9OTjaFChUyPXv2dLWdPHnSSDI7duxw2ze3zdhkdpzGGLNt2zaTN29es2HDhhypNzMyO8auXbuaAgUKGEnm3//+t/n9999ztG5PeTrOy5cvm9q1a5tFixYZY7L3X2dZKTNfzxdeeMF88cUX5quvvjIvv/yycTqdZsaMGTleuyc8HWd0dLTJly+fqVatmlm/fr3ZsWOHadGihalWrZpJSkryxhAy5EZ/B9WoUcMMGjQow683bdo0Exoa6vb6f5+x6dGjh2s9NTXVBAcHm1mzZl3zmJMnTzb16tVzrXs6YxMaGmpSUlJcbdWqVTP33HOPa/3qe7Js2TJjjDFz5841gYGBJiEhwdXngw8+MHny5DGnTp0yxhhTunRp88orr7i2//HHH6ZcuXKuGZvLly+bggULmu3bt7vV89hjj5lHH33UGJO9vxO4xsZLateu7fp/Pz8/3XLLLbr99ttdbSVLlpSkDF2w5ssyO84DBw6oY8eOioqKUuvWrXOm2EzKzBinTZumqKgoHTlyRJGRkRoxYoTefPPNnCs6EzwZZ2RkpGrUqKEePXrkeJ03ytOv55gxY1zb6tatq8TERE2ePFlPPvlkDlWcOZ6MMzU1VX/88Ydmzpzp+nlctmyZSpUqpc2bN6tNmzY5W7wHMvs7aMeOHfruu++0aNGibKvH4XCoVKlSbq+9YsUKzZw5U8eOHVNCQoKSk5NVtGjRTL9ezZo13WZMS5YsqVq1arnWr74nV2v47rvvVKdOHRUqVMjVp0mTJkpNTdXhw4fl7++vkydPqlGjRq7tefPmVf369V0zlUePHtWlS5fUqlUrt1quXLmiunXrZnosGUWw8ZJ8+fK5rTscDrc2h8MhSUpNTc3RurJaZsZ56NAhtWjRQgMGDNDzzz+fM4XegMyMsVSpUipVqpSqV6+u4sWL65577tGYMWNUunTpnCk6EzwZ56effqr9+/dr1apVkuT6hVeiRAk999xzGj9+fA5V7bkb/dls1KiRJkyYoKSkJDmdzuwr9AZ5Ms6r35e33Xaba3tQUJBKlCih48eP50C1mZfZr+d//vMf3XHHHapXr16213P1tXfs2KHu3btr/PjxatOmjQICArR8+XJNmTIlS1/vejVkhYSEBEnSBx98oLJly7pty4mfCYINfMrBgwd17733Kjw8XBMnTvR2OTni6i+UpKQkL1eSdd599139/vvvrvU9e/aob9+++vzzz1W5cmUvVpb9vvnmGwUGBvp0qPFUkyZNJEmHDx9WuXLlJEnnzp3Tr7/+qtDQUG+Wli0SEhL0zjvvKDo6Okdfd/v27QoNDdVzzz3navv5559ztIYaNWooJiZGiYmJrlmbbdu2KU+ePKpWrZoCAgJUunRp7dq1S//6178kScnJydq7d6/uvPNOSX8GYKfTqePHj3vl+kGCTS5x9OhRJSQk6NSpU/r999/1zTffSPrzGyh//vxeri5rHDhwQPfee6/atGmjESNG6NSpU5L+nCoNCgrycnVZ48MPP9Tp06fVoEEDFS5cWAcPHtSoUaPUpEkTVahQwdvlZZm/h5dff/1V0p+/NG26Ffr999/X6dOnddddd8nf318bN27USy+9pJEjR3q7tCxVtWpVdezYUcOGDdPcuXNVtGhRRUZGqnr16mrevLm3y8tyK1asUHJyco6fSq1SpYqOHz+u5cuXq0GDBvrggw/SXJSb3bp3766oqCiFh4dr3LhxOnv2rIYOHaqePXu6TtsNGzZML7/8sqpUqaLq1atr6tSpbs+oKlKkiEaOHKmIiAilpqaqadOmiouL07Zt21S0aFGFh4dn6xgINrlEv3793K4mv3qe8scff7TmD+KqVat09uxZLV68WIsXL3a1h4aGWvO8jAIFCmjevHmKiIhQUlKSQkJC9NBDD2n06NHeLg2ZkC9fPr3xxhuKiIiQMUa33nqrpk6dqv79+3u7tCy3cOFCRUREqH379sqTJ4/CwsK0fv36NKc1bPDWW2/poYceyvEQfv/99ysiIkJDhgxRUlKS2rdvrzFjxmjcuHE5VkPBggW1YcMGDRs2TA0aNFDBggXVqVMnTZ061dXnqaee0smTJxUeHq48efKob9++evDBBxUXF+fqM2HCBAUFBSk6Olo//PCDihUrpjvvvFPPPvtsto/BYUwuuC8RAAAgA3LPwyUAAAD+AcEGAIBcqHDhwtdcPv/8c2+X5zWcigIAIBc6evToNbeVLVtWBQoUyMFqfAfBBgAAWINTUQAAwBoEGwAAYA2CDQAAsAbBBgAAWINgAwAArEGwAQAA1iDYAAAAa/w/pgO73RN3smwAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt; plt.rcdefaults()\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "models = ('m1', 'm2', 'm3', 'm4', 'm5', 'm6','m7', 'final_model')\n",
    "y_pos = np.arange(len(models))\n",
    "rmse = [9.431,8.408,9.657,9.657,5.588,5.906,5.759,4.653]\n",
    "\n",
    "plt.bar(y_pos, rmse, align='center', alpha=0.5)\n",
    "plt.xticks(y_pos, models)\n",
    "plt.ylabel('RMSE')\n",
    "plt.title('RMSE Model Summary')\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "10.3.2019-DRAFT-_1 - FeatEnG - LABS.ipynb",
   "provenance": [],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}